package com.ysstech.reportworld.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ysstech.common.cache.RedisCacheManager;
import com.ysstech.common.entity.PageBean;
import com.ysstech.common.entity.SheetDTO;
import com.ysstech.common.entity.TreeNode;
import com.ysstech.common.enums.YssEnum;
import com.ysstech.common.exception.BusinessException;
import com.ysstech.common.service.DicvalueService;
import com.ysstech.common.util.DateUtil;
import com.ysstech.common.util.JdbcUtil;
import com.ysstech.common.util.StringUtil;
import com.ysstech.common.util.fileutil.FileUtil;
import com.ysstech.reportworld.entity.*;
import com.ysstech.reportworld.enums.ReportWorldDataBaseEnum;
import com.ysstech.reportworld.enums.ReportWorldEnum;
import com.ysstech.reportworld.enums.ReportWorldStatusEnum;
import com.ysstech.reportworld.mapper.DataCustomerMapper;
import com.ysstech.reportworld.mapper.DatabaseMapper;
import com.ysstech.reportworld.mapper.DataModularMapper;
import com.ysstech.reportworld.mapper.DataReportMapper;
import com.ysstech.reportworld.service.*;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.Date;

/**
 * <p>
 * 报表世界基础数据结构 服务实现类
 * </p>
 * 数据类型都是MYSQL的，占不支持ORACLE
 *
 * @author lishuangliang
 * @since 2022-12-26
 */
@Service
public class DatabaseServiceImpl extends ServiceImpl<DatabaseMapper, Database> implements DatabaseService {
    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;
    @Autowired
    private DatabaseConfigService databaseConfigService;
    @Autowired
    public DataDemandBugService dataDemandBugService;
    @Autowired
    private DataCustomerMapper dataCustomerMapper;
    @Autowired
    private DataModularMapper dataModularMapper;
    @Autowired
    private DataReportMapper dataReportMapper;
    @Autowired
    private RedisCacheManager redisCacheManager;
    @Autowired
    private DataCustomerService dataCustomerService;
    @Autowired
    private DataModularService dataModularService;
    @Autowired
    private DataReportService dataReportService;
    @Autowired
    private DicvalueService dicvalueService;
    @Autowired
    public DemandBugInfoService demandBugInfoService;
    @Autowired
    private CustomerService customerService;
    @Autowired
    private ModularService modularService;
    @Autowired
    private ReportService reportService;
    @Autowired
    private TableCheckService tableCheckService;
    @Autowired
    private TableService tableService;

    /**
     * MYSQL关键字
     */
    private String MYSQL_KEY = "KEY,VALUE,LIMIT,DESCRIBE";
    // 报表世界的admin 角色ID
    private String REPORT_WORLD_ROLEID = "df5b48a0ed134dd081d0ab8142941da4";

    /**
     * 查询基础数据树信息
     *
     * @return
     */
    public List<TreeNode> queryDatabaseTree() throws Exception {
        List<TreeNode> list = new ArrayList<>();
        QueryWrapper<Database> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("SEQUENCE");
        List<Database> listDatabase = this.list(wrapper);
        if (CollectionUtils.isEmpty(listDatabase)) {
            return list;
        }
        list.addAll(this.recursionTree("0", listDatabase));
        return list;
    }

    private List<TreeNode> recursionTree(String id, List<Database> listDatabase) throws Exception {
        List<TreeNode> list = new ArrayList<>();
        for (Database database : listDatabase) {
            if (!id.equals(database.getParentId())) {
                continue;
            }
            TreeNode treeNode = databaseToNode(database);
            List<TreeNode> children = recursionTree(database.getId(), listDatabase);
            if (null == children || children.size() == 0) {
                treeNode.setState("open");
            } else {
                treeNode.setChildren(children);
            }
            list.add(treeNode);
        }
        return list;
    }

    /**
     * 将业务功能转化成TreeNode
     */
    private TreeNode databaseToNode(Database database) {
        TreeNode node = new TreeNode();
        node.setId(database.getId());
        node.setText(database.getName());
        if (StringUtils.isNotBlank(database.getCode())) {
            node.setText(database.getName() + "【" + database.getCode().replace("RW_DB_", "") + "】");
        }
        node.setState("closed");
        node.setChecked(false);
        node.setIconCls("icon-bricks");
        node.setPId(database.getParentId());
        node.setAttributes(database.getCode());
        return node;
    }

    /**
     * 查询所有下级基础信息的id
     *
     * @param id
     * @return
     * @throws Exception
     */
    public List<String> queryDatabaseIds(String id) throws Exception {
        if (StringUtils.isBlank(id)) {
            throw new BusinessException("获取基础信息失败，请稍后重试！");
        }
        List<String> strList = new ArrayList<>();
        strList.add(id);
        List<Database> list = this.list();
        recursionIds(id, strList, list);
        return strList;
    }

    private void recursionIds(String orgId, List<String> strList, List<Database> list) {
        for (Database database : list) {
            if (!orgId.equals(database.getParentId())) {
                continue;
            }
            strList.add(database.getId());
            recursionIds(database.getId(), strList, list);
        }
    }

    /**
     * 通过sql查询数据 分页
     *
     * @param code
     * @param queryJson
     * @param pageBean
     * @return
     * @throws Exception
     */
    public Map<String, Object> queryDataBaseByCode(String code, String queryJson, PageBean pageBean) throws Exception {
        DatabaseConfig config = databaseConfigService.selectDatabaseConfig(code);
        if (null == config) {
            throw new BusinessException("没有获取到对应的基础数据配置信息，请稍后重试！");
        }
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            Map<String, Object> map = new HashMap<>();
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + config.getCode(), connection);
            if (CollectionUtils.isEmpty(columnNames)) {
                throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
            }
            // 要显示成大字段的字段处理
            for (Map.Entry<String, String> entry : columnNames.entrySet()) {
                if (ReportWorldDataBaseEnum.checkReportWorldDataBase(config.getCode(), entry.getKey())) {
                    columnNames.put(entry.getKey(), "LONGTEXT");
                }
            }
            String sql = this.sqlBuffer(queryJson, config, columnNames);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            pStemt = connection.prepareStatement(sql + "  limit  " +
                    (pageBean.getPageNum() - 1) * pageBean.getPageSize() + "," + pageBean.getPageSize());
            rs = pStemt.executeQuery();
            List<Object> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> stringMap = new HashMap<>();
                int a = 1;
                for (Map.Entry<String, String> info : columnNames.entrySet()) {
                    if (null == info) {
                        continue;
                    }
                    String colum = info.getKey();
                    String columValue = rs.getString(a);
                    stringMap.put(colum.toUpperCase(), columValue);
                    // 显示信息的所属模块，客户，报表信息
                    this.getModularCustomerReport(config, colum, columValue, stringMap);
                    if ("RW_DB_STATUS".equals(colum) && StringUtils.isNotBlank(columValue)) {
                        stringMap.put("STR_RW_DB_STATUS", ReportWorldStatusEnum.getEnumDesc(Integer.valueOf(columValue)));
                    }
                    a++;
                    continue;
                }
                list.add(stringMap);
            }
            map.put("rows", list);
            pStemt = connection.prepareStatement("select count(1) as count from (" + sql + ")  t");
            ResultSet rs1 = pStemt.executeQuery();
            if (rs1.next()) {
                map.put("total", rs1.getBigDecimal("count"));
            }
            map.put("columnNames", columnNames);
            return map;
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
    }

    /**
     * 查询的sql拼接
     *
     * @return
     */
    private String sqlBuffer(String queryJson, DatabaseConfig config, Map<String, String> columnNames) {
        StringBuffer sql = new StringBuffer();
        String dataBaseCode = config.getCode().toUpperCase();
        sql.append("SELECT DISTINCT A.* FROM " + config.getCode() + " A")
                .append(" LEFT JOIN REPORT_WORLD_DATA_REPORT B ON A." + config.getPrimaryKeyColumn() + "= B.DATA_ID AND B.DATA_CODE = '" + dataBaseCode + "'")
                .append(" LEFT JOIN REPORT_WORLD_DATA_MODULAR C ON A." + config.getPrimaryKeyColumn() + "= C.DATA_ID AND C.DATA_CODE = '" + dataBaseCode + "'")
                .append(" LEFT JOIN REPORT_WORLD_DATA_CUSTOMER D ON A." + config.getPrimaryKeyColumn() + "= D.DATA_ID AND D.DATA_CODE = '" + dataBaseCode + "'")
                .append(" WHERE 1=1");
        if (StringUtils.isNotBlank(queryJson)) {
            Map<String, Object> mapQuery = JSON.parseObject(queryJson, LinkedHashMap.class);
            // 报表
            if (!CollectionUtils.isEmpty(mapQuery) && mapQuery.containsKey("strReport") && null != mapQuery.get("strReport")) {
                String ids = (String) mapQuery.get("strReport");
                StringJoiner bufferField = this.joinerIds(ids);
                if (null != bufferField) {
                    sql.append("   AND B.REPORT_ID IN (" + bufferField + ")");
                }
            }
            // 模块
            if (!CollectionUtils.isEmpty(mapQuery) && mapQuery.containsKey("strModular") && null != mapQuery.get("strModular")) {
                String ids = (String) mapQuery.get("strModular");
                StringJoiner bufferField = this.joinerIds(ids);
                if (null != bufferField) {
                    sql.append("   AND C.MODULAR_ID IN (" + bufferField + ")");
                }
            }
            // 客户
            if (!CollectionUtils.isEmpty(mapQuery) && mapQuery.containsKey("strCustomer") && null != mapQuery.get("strCustomer")) {
                String ids = (String) mapQuery.get("strCustomer");
                StringJoiner bufferField = this.joinerIds(ids);
                if (null != bufferField) {
                    sql.append("   AND D.CUSTOMER_ID IN (" + bufferField + ")");
                }
            }
            // 状态
            if (!CollectionUtils.isEmpty(mapQuery) && mapQuery.containsKey("rwDbStatus") && null != mapQuery.get("rwDbStatus")) {
                String rwDbStatus = (String) mapQuery.get("rwDbStatus");
                if (StringUtils.isNotBlank(rwDbStatus)) {
                    sql.append("   AND A.RW_DB_STATUS = " + rwDbStatus + "");
                }
            }
            // 查询条件
            if (!CollectionUtils.isEmpty(mapQuery) && null != mapQuery.get("selectColumn")) {
                String jsonSelect = mapQuery.get("selectColumn").toString();
                Map<String, String> mapSelect = JSON.parseObject(jsonSelect, LinkedHashMap.class);
                Map<String, String> mapSelectColumn = JSON.parseObject(config.getSelectColumn(), LinkedHashMap.class);
                if (!CollectionUtils.isEmpty(mapSelect) && !CollectionUtils.isEmpty(mapSelectColumn)) {
                    for (Map.Entry<String, String> entry : mapSelect.entrySet()) {
                        if (null == entry || StringUtils.isBlank(entry.getKey()) || StringUtils.isBlank(entry.getValue())) {
                            continue;
                        }
                        if ("LIKE".equals(mapSelectColumn.get(entry.getKey()))) {
                            sql.append("   AND A." + entry.getKey() + " LIKE '%" + entry.getValue() + "%'");
                        } else if ("=".equals(mapSelectColumn.get(entry.getKey()))) {
                            if (CollectionUtils.isEmpty(columnNames)) {
                                continue;
                            }
                            String type = columnNames.get(entry.getKey());
                            switch (type) {
                                case "VARCHAR":
                                    sql.append("   AND A." + entry.getKey() + " = '" + entry.getValue() + "'");
                                    break;
                                case "DECIMAL":
                                    sql.append("   AND A." + entry.getKey() + " = " + entry.getValue());
                                    break;
                                case "DATETIME":
                                    sql.append("  AND A." + entry.getKey() + " =  TIMESTAMP ('" + entry.getValue() + "')");
                                    break;
                                case "DATE":
                                    sql.append("   AND A." + entry.getKey() + " = DATE_FORMAT(" + entry.getValue() + ",''%Y-%m-%d'')");
                                    break;
                                default:
                                    sql.append("   AND A." + entry.getKey() + " = '" + entry.getValue() + "'");
                            }
                        }
                    }
                }
            }
        }
        // 拼接排序字段
        StringJoiner bufferField = new StringJoiner(",");
        bufferField.add(" A.RW_DB_STATUS DESC");
        if (StringUtils.isNotBlank(config.getOrderColumn())) {
            String[] arrOrder = config.getOrderColumn().split(",");
            for (String column : arrOrder) {
                if (StringUtils.isBlank(column)) {
                    continue;
                }
                if (StringUtil.oneCase(column, MYSQL_KEY)) {
                    column = "`" + column + "`";
                }
                bufferField.add(column);
            }
        }
        bufferField.add(" B.REPORT_ID");
        bufferField.add(" C.MODULAR_ID");
        bufferField.add(" D.CUSTOMER_ID");
        sql.append(" ORDER BY " + bufferField);
        return sql.toString();
    }

    /**
     * 拼接查询条件的id
     *
     * @param ids
     * @return
     */
    private StringJoiner joinerIds(String ids) {
        if (StringUtils.isBlank(ids)) {
            return null;
        }
        List<String> list = JSONArray.parseArray(ids, String.class);
        if (!CollectionUtils.isEmpty(list)) {
            StringJoiner bufferField = new StringJoiner(",");
            for (String id : list) {
                bufferField.add("'" + id + "'");
            }
            return bufferField;
        }
        return null;
    }


    /**
     * 获取数据对应的模块，客户，报表信息
     *
     * @param config
     * @param colum
     * @param columValue
     * @param stringMap
     */
    private void getModularCustomerReport(DatabaseConfig config, String colum, String columValue, Map<String, Object> stringMap) {
        String primaryKey = config.getPrimaryKeyColumn();
        // 用主键字段查询对应的客户，模块，报表信息
        if (colum.equals(primaryKey.toUpperCase())) {
            // 所属模块
            DataModular dataModular = new DataModular();
            dataModular.setDataId(columValue);
            dataModular.setDataCode(config.getCode().toUpperCase());
            List<DataModular> modularList = dataModularMapper.selectPrimaryByDataId(dataModular);
            StringBuffer modularName = new StringBuffer();
            StringBuffer modularId = new StringBuffer();
            for (DataModular modular : modularList) {
                modularId.append(modular.getModularId()).append(",");
                modularName.append(modular.getModularName()).append("/");
            }
            if (StringUtils.isNotBlank(modularName.toString())) {
                stringMap.put("STRMODULAR", modularName.deleteCharAt(modularName.length() - 1).toString());
            }
            if (StringUtils.isNotBlank(modularId.toString())) {
                stringMap.put("MODULAR", modularId.deleteCharAt(modularId.length() - 1).toString());
            }
            // 所属报表
            DataReport dataReport = new DataReport();
            dataReport.setDataId(columValue);
            dataReport.setDataCode(config.getCode().toUpperCase());
            List<DataReport> reportList = dataReportMapper.selectPrimaryByDataId(dataReport);
            StringBuffer reportName = new StringBuffer();
            StringBuffer reportId = new StringBuffer();
            for (DataReport report : reportList) {
                reportId.append(report.getReportId()).append(",");
                reportName.append(report.getReportName()).append("/");
            }
            if (StringUtils.isNotBlank(reportName.toString())) {
                stringMap.put("STRREPORT", reportName.deleteCharAt(reportName.length() - 1).toString());
            }
            if (StringUtils.isNotBlank(reportId.toString())) {
                stringMap.put("REPORT", reportId.deleteCharAt(reportId.length() - 1).toString());
            }
            // 所属客户
            DataCustomer dataCustomer = new DataCustomer();
            dataCustomer.setDataId(columValue);
            dataCustomer.setDataCode(config.getCode().toUpperCase());
            List<DataCustomer> customerList = dataCustomerMapper.selectPrimaryByDataId(dataCustomer);
            StringBuffer customerName = new StringBuffer();
            StringBuffer customerId = new StringBuffer();
            for (DataCustomer customer : customerList) {
                customerId.append(customer.getCustomerId()).append(",");
                customerName.append(customer.getCustomerName()).append("/");
            }
            if (StringUtils.isNotBlank(customerName.toString())) {
                stringMap.put("STRCUSTOMER", customerName.deleteCharAt(customerName.length() - 1).toString());
            }
            if (StringUtils.isNotBlank(customerId.toString())) {
                stringMap.put("CUSTOMER", customerId.deleteCharAt(customerId.length() - 1).toString());
            }
        }
    }

    /**
     * 导出JSON的时候查询所有数据
     */
    public Map<String, List<Object>> queryAllDataList() throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            // 获取所有的基础数据信息
            List<DatabaseConfig> listConfig = databaseConfigService.selectListDatabaseConfig();
            if (CollectionUtils.isEmpty(listConfig)) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            connection = dataSource.getConnection();
            Map<String, List<Object>> map = new HashMap<>();
            for (DatabaseConfig config : listConfig) {
                if (null == config || StringUtils.isBlank(config.getCode())) {
                    continue;
                }
                if (null == connection) {
                    throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
                }
                Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + config.getCode(), connection);
                if (CollectionUtils.isEmpty(columnNames)) {
                    throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
                }
                String sql = "SELECT * FROM " + config.getCode() + " WHERE RW_DB_STATUS IN (1,2)";
                pStemt = connection.prepareStatement(sql);
                rs = pStemt.executeQuery();
                List<Object> list = new ArrayList<>();
                //  循环完成在去查询数据对应模块，客户，报表信息
                while (rs.next()) {
                    Map<String, Object> stringMap = new HashMap<>();
                    int a = 1;
                    for (Map.Entry<String, String> info : columnNames.entrySet()) {
                        if (null == info || StringUtils.isBlank(info.getKey())) {
                            a++;
                            continue;
                        }
                        String colum = info.getKey();
                        // 不导出的字段
                        if (StringUtil.oneCase(colum, "RW_DB_FILE_NAME,RW_DB_ADD_TIME,RW_DB_ADD_USER,RW_DB_EDIT_TIME,RW_DB_EDIT_USER,RW_DB_DELETE_REASON")) {
                            a++;
                            continue;
                        }
                        if ("DECIMAL".equals(info.getValue())) {
                            stringMap.put(colum, rs.getBigDecimal(a));
                        } else {
                            stringMap.put(colum, rs.getString(a));
                        }
                        a++;
                    }
                    list.add(stringMap);
                }
                map.put(config.getCode(), list);
            }
            return map;
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
    }

    /**
     * 新增基础数据
     *
     * @param code
     * @param queryJson
     * @throws Exception
     */
    public void dataBaseInsert(String code, String queryJson) throws Exception {
        Connection connection = null;
        PreparedStatement pStemtInsert = null;
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        try {
            if (StringUtils.isBlank(code) || StringUtils.isBlank(queryJson)) {
                throw new BusinessException("获取对应编码信息出错，请稍后重试！");
            }
            DatabaseConfig config = databaseConfigService.selectDatabaseConfig(code);
            if (null == config || StringUtils.isBlank(config.getCode())) {
                throw new BusinessException("没有获取到对应的配置信息，请稍后重试！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            // 页面传递的数据
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException("获取页面信息出错，请稍后重试！");
            }
            String entityJson = mapType.get("entityJson");
            if (StringUtils.isBlank(entityJson)) {
                throw new BusinessException("获取页面信息出错，请稍后重试！");
            }
            Map<String, String> mapEntity = JSON.parseObject(entityJson, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapEntity)) {
                throw new BusinessException("获取页面信息出错，请稍后重试！");
            }
            connection.setAutoCommit(false);
            // 读取数据库的字段类型
            Map<String, String> columnMap = JdbcUtil.getTableColumnNames("SELECT * FROM " + config.getCode(), connection);
            StringJoiner bufferFieldInsert = new StringJoiner(",");
            StringJoiner bufferFieldValueInsert = new StringJoiner(",");
            List<Object> listInsert = new ArrayList<>();
            StringBuffer insertSql = new StringBuffer();
            insertSql.append("INSERT INTO " + config.getCode()).append("(");
            StringBuffer selectSql = new StringBuffer();
            selectSql.append(" SELECT MAX(");
            List<String> dataTypeName = new ArrayList<>(); // 字段对应的类型
            String primaryKeyColumn = "";
            mapEntity.put("RW_DB_ADD_TIME", DateUtil.dateToStrByFormat(new Date(), YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode()));
            mapEntity.put("RW_DB_ADD_USER", redisCacheManager.getLoginUserInfo().getLoginId());
            for (Map.Entry<String, String> info : mapEntity.entrySet()) {
                if (null == info || StringUtils.isBlank(info.getKey())) {
                    continue;
                }
                String type = columnMap.get(info.getKey());
                // 真实主键字段
                if (info.getKey().equals(config.getPrimaryKeyColumn())) {
                    primaryKeyColumn = DateUtil.generateUUID();
                    listInsert.add(primaryKeyColumn);
                } else if (info.getKey().equals(config.getPrimaryKeyColumnReal())) {
                    switch (type) {
                        case "VARCHAR":
                            listInsert.add(DateUtil.generateUUID());
                            break;
                        case "DECIMAL":
                            selectSql.append(info.getKey()).append(") AS COUNT FROM ").append(config.getCode());
                            pStemtSelect = connection.prepareStatement(selectSql.toString());
                            rs = pStemtSelect.executeQuery();
                            if (rs.next()) {
                                BigDecimal numValue = rs.getBigDecimal(1);
                                if (null == numValue) {
                                    listInsert.add(1);
                                } else {
                                    BigDecimal primaryKey = numValue.add(new BigDecimal("1"));
                                    listInsert.add(primaryKey);
                                }
                            }
                            rs.close();
                            break;
                        default:
                            throw new BusinessException("获取主键的字段类型出错，请稍后重试！");
                    }
                } else {
                    listInsert.add(info.getValue());
                }
                // MYSQL关键字处理
                if (StringUtil.oneCase(info.getKey(), MYSQL_KEY)) {
                    bufferFieldInsert.add("`" + info.getKey() + "`");
                } else {
                    bufferFieldInsert.add(info.getKey());
                }
                bufferFieldValueInsert.add("?");
                dataTypeName.add(type);
            }
            insertSql.append(bufferFieldInsert).append(") VALUES (").append(bufferFieldValueInsert).append(")");
            pStemtInsert = connection.prepareStatement(insertSql.toString());
            for (int m = 0; m < listInsert.size(); m++) {
                JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(m), listInsert, pStemtInsert);
            }
            pStemtInsert.execute();
            if (StringUtils.isNotBlank(primaryKeyColumn)) {
                String ids = "[\"" + primaryKeyColumn + "\"]";
                this.insertModularCustomerReport(config.getCode(), ids, mapType);
            }
            connection.commit();
        } catch (Exception ex) {
            connection.rollback();
            throw new BusinessException("保存基础数据失败，请稍后重试！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemtInsert != null) {   // 关闭声明
                pStemtInsert.close();
            }
            if (pStemtSelect != null) {   // 关闭声明
                pStemtSelect.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
    }

    /**
     * 修改基础数据
     *
     * @param code
     * @param queryJson
     * @param ids
     * @throws Exception
     */
    public void dataBaseUpdate(String code, String queryJson, String ids) throws Exception {
        if (StringUtils.isBlank(code)) {
            throw new BusinessException("获取修改数据类型失败，请稍后重试！");
        }
        Connection connection = null;
        PreparedStatement pStemt = null;
        try {
            DatabaseConfig config = databaseConfigService.selectDatabaseConfig(code);
            if (null == config) {
                throw new BusinessException("获取修改数据类型失败，请稍后重试！");
            }
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException("获取修改数据类型失败，请稍后重试！");
            }
            // 读取数据库的字段类型
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            //开启事务
            connection.setAutoCommit(false);
            //修改一条基础数据的时候
            if (StringUtils.isNotBlank(mapType.get("entityJson"))) {
                String entityJson = mapType.get("entityJson");
                Map<String, String> entity = JSON.parseObject(entityJson, LinkedHashMap.class);
                if (CollectionUtils.isEmpty(entity)) {
                    log.error("获取页面上的修改数据信息出错，请稍后重试！");
                    return;
                }
                // 选中数据的编号
                List<String> list = JSONArray.parseArray(ids, String.class);
                // 模块
                QueryWrapper<DataModular> modularWrapper = new QueryWrapper<>();
                modularWrapper.in("DATA_ID", list);
                modularWrapper.eq("DATA_CODE", code.toUpperCase());
                dataModularService.remove(modularWrapper);
                // 客户
                QueryWrapper<DataCustomer> customerWrapper = new QueryWrapper<>();
                customerWrapper.in("DATA_ID", list);
                customerWrapper.eq("DATA_CODE", code.toUpperCase());
                dataCustomerService.remove(customerWrapper);
                // 报表
                QueryWrapper<DataReport> reportWrapper = new QueryWrapper<>();
                reportWrapper.in("DATA_ID", list);
                reportWrapper.eq("DATA_CODE", code.toUpperCase());
                dataReportService.remove(reportWrapper);
                // 拼接updatey语句
                StringBuffer updateSql = new StringBuffer();
                updateSql.append("UPDATE " + config.getCode() + " SET ");
                // 字段和值对应内容
                StringJoiner bufferField = new StringJoiner(",");
                Map<String, String> columnMap = JdbcUtil.getTableColumnNames("SELECT * FROM " + config.getCode(), connection);
                List<Object> updateList = new ArrayList<>();
                List<String> dataTypeName = new ArrayList<>();
                // 添加修改时间，修改人
                entity.put("RW_DB_EDIT_TIME", DateUtil.dateToStrByFormat(new Date(), YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode()));
                entity.put("RW_DB_EDIT_USER", redisCacheManager.getLoginUserInfo().getLoginId());
                // 拼接要修改的值
                for (Map.Entry<String, String> info : entity.entrySet()) {
                    if (null == info || StringUtils.isBlank(info.getKey())) {
                        continue;
                    }
                    // 真实主键不会被修改
                    if (info.getKey().equals(config.getPrimaryKeyColumnReal())) {
                        continue;
                    }
                    if (columnMap.containsKey(info.getKey())) {
                        dataTypeName.add(columnMap.get(info.getKey()));
                    }
                    // MYSQL关键字处理
                    if (StringUtil.oneCase(info.getKey(), MYSQL_KEY)) {
                        bufferField.add("`" + info.getKey() + "`= ?");
                    } else {
                        bufferField.add(info.getKey() + "= ?");
                    }
                    updateList.add(info.getValue());
                }
                // 拼接要主键字段
                updateSql.append(bufferField);
                for (Map.Entry<String, String> info : entity.entrySet()) {
                    if (null == info || StringUtils.isBlank(info.getKey())) {
                        continue;
                    }
                    if (!info.getKey().equals(config.getPrimaryKeyColumn())) {
                        continue;
                    }
                    if (columnMap.containsKey(info.getKey())) {
                        dataTypeName.add(columnMap.get(info.getKey()));
                    }
                    updateSql.append(" WHERE " + info.getKey()).append("=?");
                    updateList.add(info.getValue());
                    break;
                }
                pStemt = connection.prepareStatement(updateSql.toString());
                for (int m = 0; m < updateList.size(); m++) {
                    JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(m), updateList, pStemt);
                }
                pStemt.execute();
            }
            // 批量修改
            this.insertModularCustomerReport(code, ids, mapType);
            this.batchVersionAndStatus(ids, mapType, config, connection);
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            connection.rollback();
            throw new BusinessException("修改数据失败，请稍后重试！");
        } finally {
            if (connection != null) {   // 关闭声明
                connection.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }
    }

    /*
     批量修改状态和版本号
     */
    private void batchVersionAndStatus(String ids, Map<String, String> mapType, DatabaseConfig config, Connection connection) throws Exception {
        PreparedStatement pStemt = null;
        try {
            if (StringUtils.isBlank(ids)) {
                log.error("没有获取到选择的数据信息！");
                return;
            }
            // 选中数据的编号
            List<String> list = JSONArray.parseArray(ids, String.class);
            if (CollectionUtils.isEmpty(list)) {
                log.error("没有获取到选择的数据信息！");
                return;
            }
            //开启事务
            connection.setAutoCommit(false);
            // 版本号和状态
            if (StringUtils.isNotBlank(mapType.get("rwDbVersion"))) {
                String updateSql = "UPDATE " + config.getCode() + " SET RW_DB_VERSION= ?,RW_DB_EDIT_TIME=?,RW_DB_EDIT_USER=? WHERE " + config.getPrimaryKeyColumn() + "=?";
                pStemt = connection.prepareStatement(updateSql);
                for (String id : list) {
                    if (StringUtils.isBlank(id)) {
                        continue;
                    }
                    pStemt.setString(1, mapType.get("rwDbVersion"));
                    pStemt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
                    pStemt.setString(3, redisCacheManager.getLoginUserInfo().getLoginId());
                    pStemt.setString(4, id);
                    pStemt.addBatch();
                }
                if (pStemt != null) {
                    pStemt.executeBatch();
                }
            }
            if (StringUtils.isNotBlank(mapType.get("rwDbStatus"))) {
                String updateSql = "UPDATE " + config.getCode() + " SET RW_DB_STATUS= ?,RW_DB_EDIT_TIME=?,RW_DB_EDIT_USER=? WHERE " + config.getPrimaryKeyColumn() + "=?";
                pStemt = connection.prepareStatement(updateSql);
                for (String id : list) {
                    if (StringUtils.isBlank(id)) {
                        continue;
                    }
                    pStemt.setInt(1, Integer.valueOf(mapType.get("rwDbStatus")));
                    pStemt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
                    pStemt.setString(3, redisCacheManager.getLoginUserInfo().getLoginId());
                    pStemt.setString(4, id);
                    pStemt.addBatch();
                }
                if (pStemt != null) {
                    pStemt.executeBatch();
                }
            }
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            connection.rollback();
            throw new BusinessException("修改数据失败，请稍后重试！");
        } finally {
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }

    }

    /**
     * 插入数据对应的模块，客户，报表
     *
     * @param code
     * @param ids
     * @param mapType
     * @throws Exception
     */
    public void insertModularCustomerReport(String code, String ids, Map<String, String> mapType) throws Exception {
        if (StringUtils.isBlank(ids)) {
            log.error("没有获取到选择的数据信息！");
            return;
        }
        // 选中数据的编号
        List<String> list = JSONArray.parseArray(ids, String.class);
        if (CollectionUtils.isEmpty(list)) {
            log.error("没有获取到选择的数据信息！");
            return;
        }
        String strModular = mapType.get("strModular");
        String strReport = mapType.get("strReport");
        String strCustomer = mapType.get("strCustomer");
        // 查询出来的数据处理所属报表，模块，客户的信息
        if (StringUtils.isNotBlank(strModular)) {
            List<String> listModular = JSONArray.parseArray(strModular, String.class);
            // dataModulars.add(modular);
            QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
            modularQueryWrapper.in("DATA_ID", list);
            modularQueryWrapper.eq("DATA_CODE", code.toUpperCase());
            dataModularService.remove(modularQueryWrapper);
            for (String dataBaseId : list) {
                for (String idModular : listModular) {
                    DataModular modular = new DataModular();
                    modular.setModularId(idModular);
                    modular.setDataId(dataBaseId);
                    modular.setId(DateUtil.generateUUID());
                    modular.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    modular.setAddTime(LocalDateTime.now());
                    modular.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    modular.setFileName("手工");
                    modular.setDataCode(code.toUpperCase());
                    dataModularService.save(modular);
                }
            }
        }
        if (StringUtils.isNotBlank(strReport)) {
            List<String> listReport = JSONArray.parseArray(strReport, String.class);
            //报表
            QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
            reportQueryWrapper.in("DATA_ID", list);
            reportQueryWrapper.eq("DATA_CODE", code.toUpperCase());
            dataReportService.remove(reportQueryWrapper);
            for (String dataBaseId : list) {
                for (String idReport : listReport) {
                    DataReport report = new DataReport();
                    report.setReportId(idReport);
                    report.setDataId(dataBaseId);
                    report.setId(DateUtil.generateUUID());
                    report.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    report.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    report.setAddTime(LocalDateTime.now());
                    report.setFileName("手工");
                    report.setDataCode(code.toUpperCase());
                    dataReportService.save(report);
                }
            }
        }
        if (StringUtils.isNotBlank(strCustomer)) {
            List<String> listCustomer = JSONArray.parseArray(strCustomer, String.class);
            // 客户
            QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
            customerQueryWrapper.in("DATA_ID", list);
            customerQueryWrapper.eq("DATA_CODE", code.toUpperCase());
            dataCustomerService.remove(customerQueryWrapper);
            for (String dataBaseId : list) {
                for (String idCustomer : listCustomer) {
                    DataCustomer customer = new DataCustomer();
                    customer.setCustomerId(idCustomer);
                    customer.setDataId(dataBaseId);
                    customer.setId(DateUtil.generateUUID());
                    customer.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    customer.setAddTime(LocalDateTime.now());
                    customer.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    customer.setFileName("手工");
                    customer.setDataCode(code.toUpperCase());
                    dataCustomerService.save(customer);
                }
            }
        }
    }

    /**
     * 删除基础数据
     *
     * @param delJson
     */
    public void deleteDataBase(String delJson) throws Exception {
        Map<String, Object> mapType = JSON.parseObject(delJson, LinkedHashMap.class);
        if (CollectionUtils.isEmpty(mapType)) {
            throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
        }
        Connection connection = null;
        PreparedStatement pStemt = null;
        if (null == mapType.get("code")) {
            throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
        }
        connection = dataSource.getConnection();
        if (null == connection) {
            throw new BusinessException("获取数据库连接出错，请稍后重试！");
        }
        connection.setAutoCommit(false);
        DatabaseConfig config = databaseConfigService.selectDatabaseConfig(mapType.get("code").toString());
        try {
            String primaryKey = config.getPrimaryKeyColumn();
            // 传递过来的主键数据拼接
            StringBuffer buffer = new StringBuffer();
            buffer.append("DELETE FROM " + mapType.get("code") + " WHERE ");
            if (StringUtils.isBlank(primaryKey)) {
                throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
            }
            String ids = mapType.get("ids").toString();
            if (StringUtils.isBlank(ids)) {
                throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
            }
            List<String> list = JSONArray.parseArray(ids, String.class);
            if (CollectionUtils.isEmpty(list)) {
                throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
            }
            StringJoiner bufferColumkey = new StringJoiner(",");
            for (String s : list) {
                bufferColumkey.add("'" + s + "'");
            }
            buffer.append(primaryKey).append(" in(").append(bufferColumkey).append(")");
            // 按照上传得文件名删除
            QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
            modularQueryWrapper.eq("DATA_CODE", config.getCode());
            modularQueryWrapper.in("DATA_ID", list);
            dataModularService.remove(modularQueryWrapper);
            // 报表
            QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
            reportQueryWrapper.eq("DATA_CODE", config.getCode());
            reportQueryWrapper.in("DATA_ID", list);
            dataReportService.remove(reportQueryWrapper);
            // 客户
            QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
            customerQueryWrapper.eq("DATA_CODE", config.getCode());
            customerQueryWrapper.in("DATA_ID", list);
            dataCustomerService.remove(customerQueryWrapper);
            // 删除 需求BUG关联关系
            QueryWrapper<DataDemandBug> wrapper = new QueryWrapper<>();
            wrapper.eq("DATA_CODE", config.getCode());
            wrapper.in("DATA_ID", list);
            dataDemandBugService.remove(wrapper);
            pStemt = connection.prepareStatement(buffer.toString());
            pStemt.executeUpdate();
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            connection.rollback();
            throw new BusinessException(mapType.get("code") + "删除基础数据失败，请稍后重试！");
        } finally {
            if (connection != null) {   // 关闭声明
                connection.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }
    }

    /**
     * 读取基础数据文件
     *
     * @param multipartfile
     * @return
     * @throws Exception
     */
    public String dataBaseReadFile(MultipartFile multipartfile) throws Exception {
        Connection connection = null;
        PreparedStatement pStemt = null;
        StringBuffer msgInfo = new StringBuffer();
        String code = "";
        try {
            if (null == multipartfile) {
                throw new BusinessException("没有获取到文件，请稍后重试！");
            }
            String fileName = multipartfile.getOriginalFilename();
            fileName = fileName.substring(0, fileName.lastIndexOf("."));
            if (!StringUtil.oneOf(fileName, "基础数据模板表")) {
                throw new BusinessException("请选择正确的基础数据模板表文件！");
            }
            // 获取所有配置信息
            List<DatabaseConfig> list = databaseConfigService.selectListDatabaseConfig();
            if (CollectionUtils.isEmpty(list)) {
                throw new BusinessException("没有获取到对应的基础数据配置信息，请稍后重试！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            String version = dicvalueService.getMaxValue(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isBlank(version)) {
                throw new BusinessException("没有获取到系统版本号，请稍后重试！");
            }
            connection.setAutoCommit(false);
            // 查询当前版本的所有需求BUG
            QueryWrapper<DemandBugInfo> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("VERSION", version);
            List<DemandBugInfo> listDemandBugInfo = demandBugInfoService.list(queryWrapper);
            Map<String, String> mapDemandBugInfo = new HashMap<>();
            if (!CollectionUtils.isEmpty(listDemandBugInfo)) {
                for (DemandBugInfo demandBugInfo : listDemandBugInfo) {
                    if (null == demandBugInfo) {
                        continue;
                    }
                    mapDemandBugInfo.put(demandBugInfo.getDemandBugNum(), demandBugInfo.getId());
                }
            }
            for (DatabaseConfig config : list) {
                if (null == config || StringUtils.isBlank(config.getCode()) || "RW_DB_T_BASE_ROLEREGION".equals(config.getCode())) { //角色按钮关系，系统自动插入
                    continue;
                }
                code = config.getCode();
                //重复执行得时候按照文件名称删除在重新插入
                String sql = "DELETE FROM " + config.getCode() + " WHERE RW_DB_FILE_NAME='" + fileName + "'";
                pStemt = connection.prepareStatement(sql);
                pStemt.executeUpdate();
                // 读取文件对应的sheet数据
                List<List<Object>> listData = null;
                try {
                    listData = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile), config.getCode().replace("RW_DB_", "").toUpperCase());
                } catch (Exception ex) {
                    log.debug("【" + config.getCode().replace("RW_DB_", "") + "】对应的数据EXECL中不存在！");
                    continue;
                }
                if (CollectionUtils.isEmpty(listData) || listData.size() < 2) {
                    msgInfo.append(config.getCode().replace("RW_DB_", "")).append("===【修改条数0").append("】【新增条数0")
                            .append("】").append("\r\n");
                    continue;
                }
                // 按照文件名称删除对应的 模块，客户，报表，需求BUG信息
                this.deleteDataBaseByFileName(fileName, config.getCode());
                // 插入对应的数据信息
                // 数据对应的模块，客户，需求BUG信息
                Map<String, Map<String, String>> mapInfo = new LinkedHashMap<>();
                // 判断数据是否存在
                msgInfo.append(this.insertBaseDataByFile(fileName, version, listData, config, connection, mapInfo));
                // 数据对应的模块，客户，报表，需求BUG信息的新增和修改
                this.insertDataBaseSign(fileName, mapInfo, config, mapDemandBugInfo);
                // 读取完按钮信息后插入admin角色对应的按钮关联关系
                if ("RW_DB_T_BASE_REGION".equals(config.getCode())) {
                    this.insertTbaseRoleregion(fileName, connection, version);
                }
            }
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            msgInfo.append("【" + code.replace("RW_DB_", "") + "】读取文件出错！" + ex.getMessage());
            connection.rollback();
        } finally {
            if (connection != null) {   // 关闭声明
                connection.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }
        return msgInfo.toString();
    }

    /**
     * 读取文件得时候删除对应得模块，客户，报表信息，重新插入
     *
     * @param fileName 文件名称
     * @throws Exception
     */

    public void deleteDataBaseByFileName(String fileName, String code) throws Exception {
        // 按照上传得文件名删除
        // 模块
        QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
        modularQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        modularQueryWrapper.eq("FILE_NAME", fileName);
        dataModularService.remove(modularQueryWrapper);
        // 报表
        QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
        reportQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        reportQueryWrapper.eq("FILE_NAME", fileName);
        dataReportService.remove(reportQueryWrapper);
        // 客户
        QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
        customerQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        customerQueryWrapper.eq("FILE_NAME", fileName);
        dataCustomerService.remove(customerQueryWrapper);
        // 删除修改记录信息
        QueryWrapper<DataDemandBug> wrapper = new QueryWrapper<>();
        wrapper.eq("DATA_CODE", code.toUpperCase());
        wrapper.eq("FILE_NAME", fileName);
        dataDemandBugService.remove(wrapper);
    }


    /**
     * 文件数据生成对应的insert语句
     *
     * @param listData
     * @param config
     * @param connection
     * @throws Exception
     */
    public String insertBaseDataByFile(String fileName, String version, List<List<Object>> listData,
                                       DatabaseConfig config, Connection connection, Map<String, Map<String, String>> map) throws Exception {
        PreparedStatement pStemtInsert = null;
        PreparedStatement pStemtUpdate = null;
        PreparedStatement pStemtSelect = null;
        PreparedStatement pStemtSelectPrimaryKey = null;
        ResultSet rs = null;
        StringBuffer msgInfo = new StringBuffer();
        try {
            // 读取数据库的字段类型
            Map<String, String> columnMap = JdbcUtil.getTableColumnNames("SELECT * FROM " + config.getCode(), connection);
            List<Object> objects = listData.get(0);
            StringJoiner bufferFieldInsert = new StringJoiner(",");
            StringJoiner bufferFieldOnly = new StringJoiner(" ");
            StringJoiner bufferFieldValueUpdate = new StringJoiner(",");
            StringJoiner bufferFieldValueInsert = new StringJoiner(",");
            List<String> dataTypeName = new ArrayList<>();
            // 业务主键字段
            List<Integer> onlyName = new ArrayList<>();
            String[] arr = config.getOnlyColumn().split(",");
            int keyNumReal = 0;  // 标识主键在第几个
            int num = 0; // 确定唯一的字段在第几个
            for (Object object : objects) {
                if (null == object) {
                    continue;
                }
                if (config.getPrimaryKeyColumnReal().equals(object.toString())) {
                    keyNumReal = dataTypeName.size();
                }
                if (columnMap.containsKey(object.toString())) {
                    dataTypeName.add(columnMap.get(object.toString()));
                }
                // 判断唯一数据的字段
                for (String only : arr) {
                    if (only.equals(object.toString())) {
                        onlyName.add(num);
                        if (StringUtil.oneCase(object.toString(), MYSQL_KEY)) {
                            bufferFieldOnly.add(" AND IFNULL(`" + only + "`,'') = ? ");
                        } else {
                            bufferFieldOnly.add(" AND IFNULL(" + only + ",'')= ? ");
                        }
                    }
                }
                num++;
                if (StringUtil.oneOf(object.toString(), "所属模块,所属报表,所属客户,需求/BUG编号")) {
                    //dataTypeName.add(object.toString());
                    continue;
                }
                // MYSQL关键字处理
                if (StringUtil.oneCase(object.toString(), MYSQL_KEY)) {
                    bufferFieldInsert.add("`" + object.toString() + "`");
                } else {
                    bufferFieldInsert.add(object.toString());
                }
                if (!config.getPrimaryKeyColumn().equals(object.toString())) { //修改的时候主键不修改
                    if (StringUtil.oneCase(object.toString(), MYSQL_KEY)) {
                        bufferFieldValueUpdate.add("`" + object.toString() + "`= ?");
                    } else {
                        bufferFieldValueUpdate.add("" + object.toString() + " = ?");
                    }
                }
                bufferFieldValueInsert.add("?");
            }
            // 添加固定的类型文件名称，版本号，添加时间，修改时间，添加人，修改人，技术主键，和所属模块，所属报表，所属客户，需求/BUG编号 按照顺序放
            String[] arrDataTypeName = new String[]{"VARCHAR", "VARCHAR", "TIMESTAMP", "VARCHAR", "VARCHAR", "所属模块", "所属报表", "所属客户", "需求/BUG编号"};
            for (String typeName : arrDataTypeName) {
                dataTypeName.add(typeName);
            }
            //查询语句判断是否已经存在
            StringBuffer selectSqlOnly = new StringBuffer();
            selectSqlOnly.append("SELECT * FROM " + config.getCode()).append(" WHERE 1=1");
            selectSqlOnly.append(bufferFieldOnly);
            // 判断数据是不是已经存在
            pStemtSelect = connection.prepareStatement(selectSqlOnly.toString());
            // 数据不存在insert语句
            StringBuffer insertSql = new StringBuffer();
            insertSql.append("INSERT INTO " + config.getCode()).append("(");
            // 添加文件名称和版本号
            bufferFieldInsert.add("RW_DB_FILE_NAME,RW_DB_VERSION,RW_DB_ADD_TIME,RW_DB_ADD_USER,RW_DB_ID");
            insertSql.append(bufferFieldInsert).append(") VALUES (");
            bufferFieldValueInsert.add("?,?,?,?,?");
            insertSql.append(bufferFieldValueInsert).append(")");
            pStemtInsert = connection.prepareStatement(insertSql.toString());
            // 数据存在修改语句
            StringBuffer updateSql = new StringBuffer();
            updateSql.append("UPDATE " + config.getCode() + " SET ");
            // 添加文件名称和版本号
            bufferFieldValueUpdate.add("RW_DB_FILE_NAME=?,RW_DB_VERSION = ?,RW_DB_EDIT_TIME=?,RW_DB_EDIT_USER=?");
            updateSql.append(bufferFieldValueUpdate).append(" WHERE 1=1 ").append(bufferFieldOnly);
            pStemtUpdate = connection.prepareStatement(updateSql.toString());
            String keyValue = "";
            // 存放字段值和数据字段类型
            int updateNumber = 0, insertNumber = 0;
            // 判断是不是又业务主键重复的数据
            Map<String, String> dataMap = new HashMap<>();
            for (int i = 1; i < listData.size(); i++) {
                List<Object> objectList = listData.get(i);
                if (CollectionUtils.isEmpty(objectList)) {
                    continue;
                }
                List<Object> listInsert = new ArrayList<>();
                List<Object> listSelect = new ArrayList<>();
                // 存放数据对应的客户，模块，需求BUG
                Map<String, String> mapInfo = new HashMap<>();
                // 数据拼接成Sql
                for (int k = 0; k < objectList.size(); k++) {
                    Object obj = objectList.get(k);
                    //  原始的主键字段不能为空
                    if (StringUtils.isNotBlank(config.getPrimaryKeyColumnReal()) && k == keyNumReal && (null == obj || StringUtils.isBlank(obj.toString()))) {
                        throw new BusinessException("表" + config.getCode().replace("RW_DB_", "") + config.getPrimaryKeyColumnReal() + "主键字段值不能为空！");
                    }
                    // 添加了文件名称和版本号，这里加4去取
                    if (StringUtil.oneOf(dataTypeName.get(k + 5), "所属模块,所属报表,所属客户,需求/BUG编号")) {
                        if (null != obj) {
                            mapInfo.put(dataTypeName.get(k + 5), this.getSignValue(dataTypeName.get(k + 4), obj.toString()));
                        }
                        continue;
                    }
                    listInsert.add(obj);
                    for (Integer number : onlyName) {
                        if (k == number) {
                            listSelect.add(obj);
                        }
                    }
                }
                // 判断插入的数据业务主键是不是存在
                StringBuffer key = new StringBuffer();
                for (Integer integer : onlyName) {
                    key.append(listInsert.get(integer)).append("=");
                }
                for (int m = 0; m < listSelect.size(); m++) {
                    JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(onlyName.get(m)), listSelect, pStemtSelect, true);
                }
                rs = pStemtSelect.executeQuery();
                if (rs.next()) {
                    // 修改的时候不修改文件名，重复上传非导致删除
                    listInsert.add(rs.getString("RW_DB_FILE_NAME"));
                    listInsert.add(version);
                    listInsert.add(DateUtil.dateToStrByFormat(new Date(), YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode()));
                    listInsert.add(redisCacheManager.getLoginUserInfo().getLoginId());
                    int number = 0;
                    for (int m = 0; m < listInsert.size(); m++) {
                        JdbcUtil.addPreparedStatement(number, m, dataTypeName.get(m), listInsert, pStemtUpdate);
                        number++;
                    }
                    // 条件开始的
                    for (int m = 0; m < listSelect.size(); m++) {
                        JdbcUtil.addPreparedStatement(number + m, m, dataTypeName.get(onlyName.get(m)), listSelect, pStemtUpdate, true);
                    }
                    pStemtUpdate.addBatch();
                    map.put(rs.getString(config.getPrimaryKeyColumn()), mapInfo); // 存在 所属模块，所属客户，所属报表，需求信息
                    updateNumber++;
                    msgInfo.append(config.getCode().replace("RW_DB_", "")).
                            append("===【业务主键" + config.getOnlyColumn()).append("】【").append(key + "系统已经存在该条数据，该数据会被更新，请核对数据！】").append("\r\n");
                    continue;
                }
                rs.close();
                //新增的时候 添加文件名称和版本号
                listInsert.add(fileName);
                listInsert.add(version);
                listInsert.add(DateUtil.dateToStrByFormat(new Date(), YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode()));
                listInsert.add(redisCacheManager.getLoginUserInfo().getLoginId());
                if (dataMap.containsKey(key.toString())) {
                    msgInfo.append(config.getCode().replace("RW_DB_", "")).
                            append("===【业务主键" + config.getOnlyColumn()).append("】【").append(key + "存在重复数据，已经跳过改数据，请核对数据！】").append("\r\n");
                    continue;
                }
                dataMap.put(key.toString(), key.toString());
                //  校验业务主键不一样，当时主键一致的情况
                if (StringUtils.isBlank(config.getPrimaryKeyColumnReal())) {
                    pStemtSelectPrimaryKey = connection.prepareStatement("SELECT 1 FROM " + config.getCode() + " WHERE "
                            + config.getPrimaryKeyColumnReal() + " = ?");
                    for (int m = 0; m < listInsert.size(); m++) {
                        if (m != keyNumReal) {
                            continue;
                        }
                        JdbcUtil.addPreparedStatement(0, m, dataTypeName.get(m), listInsert, pStemtSelectPrimaryKey);
                    }
                    rs = pStemtSelectPrimaryKey.executeQuery();
                    if (rs.next()) {
                        msgInfo.append(config.getCode().replace("RW_DB_", "")).
                                append("===【主键" + config.getPrimaryKeyColumnReal()).append("】【").append(listInsert.get(keyNumReal) + "数据已经存在，已经跳过该数据，请核对数据！】").append("\r\n");
                        continue;
                    }
                    rs.close();
                }
                keyValue = DateUtil.generateUUID();
                listInsert.add(keyValue);
                for (int m = 0; m < listInsert.size(); m++) {
                    JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(m), listInsert, pStemtInsert);
                }
                pStemtInsert.addBatch();
                map.put(keyValue, mapInfo); // 存在 所属模块，所属客户，所属报表，需求信息
                insertNumber++;
            }
            if (null != pStemtUpdate) {
                pStemtUpdate.executeBatch();
            }
            if (null != pStemtInsert) {
                pStemtInsert.executeBatch();
            }
            msgInfo.append(config.getCode().replace("RW_DB_", "")).append("===【修改条数" + updateNumber).append("】【新增条数" + insertNumber)
                    .append("】").append("\r\n");
        } catch (Exception ex) {
            log.error(ex.getMessage());
            msgInfo.append(config.getCode().replace("RW_DB_", "") + "读取数据信息出错" + ex.getMessage()).append("\r\n");
        } finally {
            if (pStemtInsert != null) {   // 关闭声明
                pStemtInsert.close();
            }
            if (pStemtUpdate != null) {   // 关闭声明
                pStemtUpdate.close();
            }
            if (pStemtSelect != null) {   // 关闭声明
                pStemtSelect.close();
            }
            if (pStemtSelectPrimaryKey != null) {   // 关闭声明
                pStemtSelectPrimaryKey.close();
            }
            if (rs != null) {   // 关闭声明
                rs.close();
            }
        }
        return msgInfo.toString();
    }

    /**
     * 插入角色对应的按钮信息数据
     *
     * @param fileName
     * @param connection
     * @param version
     * @throws Exception
     */
    public void insertTbaseRoleregion(String fileName, Connection connection, String version) throws Exception {
        PreparedStatement pStemtDelete = null;
        PreparedStatement pStemtInsert = null;
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        try {
            // 按照文件名称删除
            pStemtDelete = connection.prepareStatement("DELETE  FROM RW_DB_T_BASE_ROLEREGION WHERE RW_DB_FILE_NAME='" + fileName + "'");
            pStemtDelete.execute();
            // 查询当前文件读取进来的按钮信息
            pStemtSelect = connection.prepareStatement("SELECT FID FROM RW_DB_T_BASE_REGION WHERE RW_DB_FILE_NAME='" + fileName + "'");
            //插入角色对应的按钮信息
            pStemtInsert = connection.prepareStatement("INSERT INTO RW_DB_T_BASE_ROLEREGION (FID,FROLE_ID,FREGION_ID," +
                    "RW_DB_FILE_NAME,RW_DB_VERSION,RW_DB_ADD_TIME,RW_DB_ADD_USER) VALUES (?,?,?,?,?,?,?)");
            rs = pStemtSelect.executeQuery();
            while (rs.next()) {
                String id = DateUtil.generateUUID();
                pStemtInsert.setString(1, id);
                pStemtInsert.setString(2, REPORT_WORLD_ROLEID);
                pStemtInsert.setString(3, rs.getString(1));
                pStemtInsert.setString(4, fileName);
                pStemtInsert.setString(5, version);
                pStemtInsert.setTimestamp(6, new java.sql.Timestamp(new Date().getTime()));
                pStemtInsert.setString(7, redisCacheManager.getLoginUserInfo().getLoginId());
                pStemtInsert.addBatch();
                // 获取按钮对应的模块，客户，报表信息
                //删除表和客户信息关联关系
                QueryWrapper<DataCustomer> wrapperCustomer = new QueryWrapper<>();
                wrapperCustomer.in("DATA_ID", rs.getString(1));
                wrapperCustomer.eq("DATA_CODE", "RW_DB_T_BASE_REGION");
                List<DataCustomer> customerList = dataCustomerService.list(wrapperCustomer);
                if (!CollectionUtils.isEmpty(customerList)) {
                    for (DataCustomer dataCustomer : customerList) {
                        dataCustomer.setId(DateUtil.generateUUID());
                        dataCustomer.setDataId(id);
                        dataCustomer.setDataCode("RW_DB_T_BASE_ROLEREGION");
                        dataCustomerService.save(dataCustomer);
                    }
                }
                //删除表和模块信息关联关系
                QueryWrapper<DataModular> wrapperModular = new QueryWrapper<>();
                wrapperModular.in("DATA_ID", rs.getString(1));
                wrapperModular.eq("DATA_CODE", "RW_DB_T_BASE_REGION");
                List<DataModular> modularList = dataModularService.list(wrapperModular);
                if (!CollectionUtils.isEmpty(modularList)) {
                    for (DataModular dataModular : modularList) {
                        dataModular.setId(DateUtil.generateUUID());
                        dataModular.setDataId(id);
                        dataModular.setDataCode("RW_DB_T_BASE_ROLEREGION");
                        dataModularService.save(dataModular);
                    }
                }
                //删除表和报表信息关联关系
                QueryWrapper<DataReport> wrapperReport = new QueryWrapper<>();
                wrapperReport.in("DATA_ID", rs.getString(1));
                wrapperReport.eq("DATA_CODE", "RW_DB_T_BASE_REGION");
                List<DataReport> reportList = dataReportService.list(wrapperReport);
                if (!CollectionUtils.isEmpty(reportList)) {
                    for (DataReport dataReport : reportList) {
                        dataReport.setId(DateUtil.generateUUID());
                        dataReport.setDataId(id);
                        dataReport.setDataCode("RW_DB_T_BASE_ROLEREGION");
                        dataReportService.save(dataReport);
                    }
                }
            }
            if (null != pStemtInsert) {
                pStemtInsert.executeBatch();
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("插入ADMIN的角色按钮关系表出错，请稍后重试！" + ex.getMessage());
        } finally {
            if (pStemtDelete != null) {   // 关闭声明
                pStemtDelete.close();
            }
            if (pStemtInsert != null) {   // 关闭声明
                pStemtInsert.close();
            }
            if (pStemtSelect != null) {   // 关闭声明
                pStemtSelect.close();
            }
            if (rs != null) {   // 关闭声明
                rs.close();
            }
        }
    }

    /**
     * 插入文件里的的所属模块，客户，报表，和需求BUG
     *
     * @param fileName
     * @param mapInfo
     * @param config
     * @param mapDemandBugInfo
     * @throws Exception
     */
    public void insertDataBaseSign(String fileName, Map<String, Map<String, String>> mapInfo, DatabaseConfig config,
                                   Map<String, String> mapDemandBugInfo) throws Exception {
        List<DataModular> dataModulars = new ArrayList<>();
        List<DataReport> dataReports = new ArrayList<>();
        List<DataCustomer> dataCustomers = new ArrayList<>();
        List<DataDemandBug> dataDemandBugs = new ArrayList<>();
        //  循环基础数据的对应模块，客户，需求bug
        for (Map.Entry<String, Map<String, String>> entry : mapInfo.entrySet()) {
            if (null == entry || StringUtils.isBlank(entry.getKey())) {
                continue;
            }
            Map<String, String> stringMap = entry.getValue();
            for (Map.Entry<String, String> entry2 : stringMap.entrySet()) {
                if (null == entry2 || StringUtils.isBlank(entry2.getKey())) {
                    continue;
                }
                if ("所属模块".equals(entry2.getKey()) && StringUtils.isNotBlank(entry2.getValue())) {
                    // 先删除在插入数据
                    QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
                    modularQueryWrapper.eq("DATA_ID", entry.getKey());
                    modularQueryWrapper.eq("MODULAR_ID", entry2.getValue());
                    modularQueryWrapper.eq("DATA_CODE", config.getCode().toUpperCase());
                    dataModularService.remove(modularQueryWrapper);
                    DataModular modular = new DataModular();
                    modular.setId(DateUtil.generateUUID());
                    modular.setDataCode(config.getCode().toUpperCase());
                    modular.setFileName(fileName);
                    modular.setDataId(entry.getKey());
                    modular.setModularId(this.getSignValue(entry2.getKey(), entry2.getValue()));
                    modular.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    modular.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    modular.setAddTime(LocalDateTime.now());
                    dataModulars.add(modular);
                    continue;
                }
                if ("所属报表".equals(entry2.getKey()) && StringUtils.isNotBlank(entry2.getValue())) {
                    QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
                    reportQueryWrapper.eq("DATA_ID", entry.getKey());
                    reportQueryWrapper.eq("REPORT_ID", entry2.getValue());
                    reportQueryWrapper.eq("DATA_CODE", config.getCode().toUpperCase());
                    dataReportService.remove(reportQueryWrapper);
                    DataReport report = new DataReport();
                    report.setId(DateUtil.generateUUID());
                    report.setDataCode(config.getCode().toUpperCase());
                    report.setFileName(fileName);
                    report.setDataId(entry.getKey());
                    report.setReportId(this.getSignValue(entry2.getKey(), entry2.getValue()));
                    report.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    report.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    report.setAddTime(LocalDateTime.now());
                    dataReports.add(report);
                    continue;
                }
                if ("所属客户".equals(entry2.getKey()) && StringUtils.isNotBlank(entry2.getValue())) {
                    QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
                    customerQueryWrapper.eq("DATA_ID", entry.getKey());
                    customerQueryWrapper.eq("CUSTOMER_ID", entry2.getValue());
                    customerQueryWrapper.eq("DATA_CODE", config.getCode().toUpperCase());
                    dataCustomerService.remove(customerQueryWrapper);
                    DataCustomer customer = new DataCustomer();
                    customer.setId(DateUtil.generateUUID());
                    customer.setDataCode(config.getCode().toUpperCase());
                    customer.setFileName(fileName);
                    customer.setDataId(entry.getKey());
                    customer.setCustomerId(this.getSignValue(entry2.getKey(), entry2.getValue()));
                    customer.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    customer.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    customer.setAddTime(LocalDateTime.now());
                    dataCustomers.add(customer);
                    continue;
                }
                // 修改记录信息
                if ("需求/BUG编号".equals(entry2.getKey()) && StringUtils.isNotBlank(entry2.getValue())
                        && !CollectionUtils.isEmpty(mapDemandBugInfo)) {
                    String bugId = mapDemandBugInfo.get(entry2.getValue());
                    if (StringUtils.isBlank(bugId)) {
                        continue;
                    }
                    QueryWrapper<DataDemandBug> updateInfoQueryWrapper = new QueryWrapper<>();
                    updateInfoQueryWrapper.eq("DATA_ID", entry.getKey());
                    updateInfoQueryWrapper.eq("DEMAND_BUG_ID", bugId);
                    updateInfoQueryWrapper.eq("DATA_CODE", config.getCode().toUpperCase());
                    dataDemandBugService.remove(updateInfoQueryWrapper);
                    DataDemandBug dataDemandBug = new DataDemandBug();
                    dataDemandBug.setId(DateUtil.generateUUID());
                    dataDemandBug.setDataCode(config.getCode().toUpperCase());
                    dataDemandBug.setDemandBugId(bugId);
                    dataDemandBug.setDataId(entry.getKey());
                    dataDemandBug.setDataCode(config.getCode().toUpperCase());
                    dataDemandBug.setFileName(fileName);
                    dataDemandBug.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    dataDemandBug.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    dataDemandBug.setAddTime(LocalDateTime.now());
                    dataDemandBugs.add(dataDemandBug);
                    continue;
                }
            }
        }
        if (!CollectionUtils.isEmpty(dataModulars)) {
            dataModularService.saveBatch(dataModulars);
        }
        if (!CollectionUtils.isEmpty(dataReports)) {
            dataReportService.saveBatch(dataReports);
        }
        if (!CollectionUtils.isEmpty(dataCustomers)) {
            dataCustomerService.saveBatch(dataCustomers);
        }
        if (!CollectionUtils.isEmpty(dataDemandBugs)) {
            dataDemandBugService.saveBatch(dataDemandBugs);
        }
    }


    /**
     * 表格里的模块，客户，报表，转换成库里的id
     *
     * @param key
     * @param value
     * @return
     */
    public String getSignValue(String key, String value) throws Exception {
        if (StringUtils.isBlank(value)) {
            return value;
        }
        // 所属模块
        if (key.equals("所属模块")) {
            Map<String, Modular> map = modularService.getModularMap();
            for (Map.Entry<String, Modular> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        // 所属客户
        if (key.equals("所属客户")) {
            Map<String, Customer> map = customerService.getCustomerMap();
            for (Map.Entry<String, Customer> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        // 所属报表
        if (key.equals("所属报表")) {
            Map<String, Report> map = reportService.getReportMap();
            for (Map.Entry<String, Report> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        return value;
    }

    /**
     * 导出SQL语句
     *
     * @param queryJson
     * @return
     * @throws Exception
     */
    public String dataBaseSql(String queryJson, String code) throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        StringBuffer buffer = new StringBuffer();
        try {
            if (StringUtils.isBlank(code)) {
                throw new BusinessException("没有获取到要导出数据的类型，请稍后重试！");
            }
            DatabaseConfig databaseConfig = databaseConfigService.selectDatabaseConfig(code);
            if (null == databaseConfig || StringUtils.isBlank(databaseConfig.getCode())) {
                throw new BusinessException("没有获取到对应的基础数据配置信息，请稍后重试！");
            }
            String json = databaseConfig.getShowColumn();
            if (StringUtils.isBlank(json)) {
                throw new BusinessException(code + "还没有维护对应的字段信息！");
            }
            Map<String, String> mapType = JSON.parseObject(json, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException(code + "没有获取到维护展示字段信息！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + databaseConfig.getCode(), connection);
            if (CollectionUtils.isEmpty(columnNames)) {
                throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
            }
            String sql = this.sqlBuffer(queryJson, databaseConfig, columnNames);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            String sqlType = "ORACLE";
            if (StringUtils.isNotBlank(queryJson)) {
                Map<String, String> mapQuery = JSON.parseObject(queryJson, LinkedHashMap.class);
                sqlType = mapQuery.get("sqlType");
                if (StringUtils.isBlank(sqlType)) {
                    sqlType = "ORACLE";
                }
            }
            pStemt = connection.prepareStatement(sql);
            rs = pStemt.executeQuery();
            while (rs.next()) {
                int a = 1;
                StringBuffer insertSql = new StringBuffer();
                insertSql.append("INSERT INTO " + databaseConfig.getCode().replace("RW_DB_", "")).append("(");
                StringJoiner bufferFieldInsert = new StringJoiner(",");
                StringJoiner bufferFieldValueInsert = new StringJoiner(",");
                List<String> dataTypeName = new ArrayList<>(); // 字段对应的类型
                for (Map.Entry<String, String> info : columnNames.entrySet()) {
                    if (null == info || !mapType.containsKey(info.getKey())) {
                        continue;
                    }
                    String value = rs.getString(a);
                    if (StringUtils.isBlank(value)) {
                        bufferFieldInsert.add(info.getKey());
                        bufferFieldValueInsert.add(null);
                        dataTypeName.add(info.getValue());
                        a++;
                        continue;
                    }
                    switch (info.getValue()) {
                        case "VARCHAR":
                            value = "'" + value + "'";
                            break;
                        case "DECIMAL":
                            break;
                        case "DATETIME":
                            value = "TIMESTAMP('" + value + "')";
                            break;
                        case "DATE":
                            value = "TO_DATE(" + value + ",'" + YssEnum.YSS_DEFAULT_DATE_TIME.getCode() + "')";
                            if ("MYSQL".equals(sqlType)) {
                                value = "DATE_FORMAT(" + value + ",'%Y-%m-%d')";
                            }
                    }
                    if ("MYSQL".equals(sqlType) && StringUtil.oneCase(info.getKey(), MYSQL_KEY)) {
                        bufferFieldInsert.add("`" + info.getKey() + "`");
                    } else {
                        bufferFieldInsert.add(info.getKey());
                    }
                    bufferFieldValueInsert.add(value);
                    dataTypeName.add(info.getValue());
                    a++;
                }
                insertSql.append(bufferFieldInsert).append(") VALUES (").append(bufferFieldValueInsert).append(");");
                buffer.append(insertSql).append("\r\n");
            }
        } catch (Exception ex) {
            connection.rollback();
            throw new BusinessException("生成插入语句失败！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
        return buffer.toString();
    }


    /**
     * 导出EXECL文件,按照配置的显示字段信息导出
     *
     * @param queryJson
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> dataBaseExecl(String queryJson, String code) throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            if (StringUtils.isBlank(code)) {
                throw new BusinessException("没有获取到要导出数据的类型，请稍后重试！");
            }
            DatabaseConfig databaseConfig = databaseConfigService.selectDatabaseConfig(code);
            if (null == databaseConfig || StringUtils.isBlank(databaseConfig.getCode())) {
                throw new BusinessException("没有获取到对应的基础数据配置信息，请稍后重试！");
            }
            String json = databaseConfig.getShowColumn();
            if (StringUtils.isBlank(json)) {
                throw new BusinessException(code + "还没有维护对应的字段信息！");
            }
            Map<String, String> mapType = JSON.parseObject(json, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException(code + "没有获取到维护展示字段信息！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + databaseConfig.getCode(), connection);
            if (CollectionUtils.isEmpty(columnNames)) {
                throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
            }
            String sql = this.sqlBuffer(queryJson, databaseConfig, columnNames);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            pStemt = connection.prepareStatement(sql);
            rs = pStemt.executeQuery();
            List<Map<String, Object>> list = new ArrayList();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (Map.Entry<String, String> entry : mapType.entrySet()) {
                    if (null == entry || StringUtils.isBlank(entry.getKey())
                            || StringUtils.isBlank(entry.getValue())) {
                        continue;
                    }
                    row.put(entry.getKey(), rs.getString(entry.getKey()));
                }
                list.add(row);
            }
            return list;
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("生成EXECL文件失败！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
    }


    /**
     * 重新生成基础数据的校验语句，检查数据的所属模块，所属客户，所属报表信息
     *
     * @throws Exception
     */
    public void updateCheckDataBase() throws Exception {
        try {
            // 删除自动生成的
            QueryWrapper<TableCheck> checkQueryWrapper = new QueryWrapper<>();
            checkQueryWrapper.eq("FILENAME", "voluntarilyAdd");
            tableCheckService.remove(checkQueryWrapper);
            List<DatabaseConfig> list = databaseConfigService.selectListDatabaseConfig();
            if (CollectionUtils.isEmpty(list)) {
                return;
            }
            // 获取现在最大的版本号
            String version = dicvalueService.getMaxValue(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isBlank(version)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            for (DatabaseConfig config : list) {
                if (null == config || StringUtils.isBlank(config.getOnlyColumn())) {
                    continue;
                }
                String tableName = config.getCode().replace("RW_DB_", "");
                // 查询表ID
                QueryWrapper<Table> wrapper = new QueryWrapper<>();
                wrapper.eq("NAME", tableName);
                Table table = tableService.getOne(wrapper);
                if (null == table || StringUtils.isBlank(table.getId())) {
                    continue;
                }
                String sql = "SELECT * FROM " + config.getCode() + " A WHERE  A.RW_DB_STATUS = 1 " +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_MODULAR B  " +
                        " WHERE B.DATA_ID = A." + config.getPrimaryKeyColumn() + "  AND B.DATA_CODE = '" + config.getCode() + "')" +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_REPORT C " +
                        " WHERE C.DATA_ID = A." + config.getPrimaryKeyColumn() + "  AND C.DATA_CODE = '" + config.getCode() + "')" +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_CUSTOMER D " +
                        " WHERE D.DATA_ID = A." + config.getPrimaryKeyColumn() + "  AND D.DATA_CODE = '" + config.getCode() + "')";
                TableCheck tableCheck = new TableCheck();
                tableCheck.setId(DateUtil.generateUUID());
                tableCheck.setDatasql(sql);
                tableCheck.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                tableCheck.setAddTime(LocalDateTime.now());
                tableCheck.setFileName("voluntarilyAdd");
                tableCheck.setErrorType(2);
                tableCheck.setErrorMsg(config.getDataBaseName() + "【" + config.getCode().replace("RW_DB_", "") + "】中的数据没有对应所属模块，所属客户，所属报表信息，请检查！");
                tableCheck.setSequence(99);
                tableCheck.setTableId(table.getId());
                tableCheck.setVersion(version);
                tableCheck.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                tableCheckService.save(tableCheck);
                this.updateCheckDataBase2(config, table.getId(), version);
            }
        } catch (Exception ex) {
            throw new BusinessException("检查表数据信息出错：" + ex.getMessage());
        } finally {
        }
    }

    /**
     * 业务主键相同的数据 所属模块，所属客户，所属报表信息相同检查
     *
     * @throws Exception
     */
    private void updateCheckDataBase2(DatabaseConfig config, String tableId, String version) throws Exception {
        try {
            String[] arr = config.getOnlyColumn().split(",");
            if (null == arr || arr.length == 0) {
                return;
            }
            StringJoiner bufferOnlyColumn = new StringJoiner(",");
            for (String colum : arr) {
                if (StringUtils.isBlank(colum)) {
                    continue;
                }
                bufferOnlyColumn.add("A." + colum);
            }
            StringBuffer andBuffer = new StringBuffer();
            for (int i = 0; i < arr.length; i++) {
                if (0 == i) {
                    andBuffer.append(" ON T1." + arr[i] + " = T2." + arr[i] + "");
                    continue;
                }
                andBuffer.append(" AND T1." + arr[i] + " = T2." + arr[i] + "");
            }
            String sql = "SELECT A.*, B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID FROM(" +
                    " SELECT T1.* FROM " + config.getCode() + " T1 " +
                    " INNER JOIN (SELECT " + config.getPrimaryKeyColumn() + "," + bufferOnlyColumn +
                    " FROM " + config.getCode() + " A WHERE A.RW_DB_STATUS = 1" +
                    " GROUP BY " + bufferOnlyColumn +
                    " HAVING COUNT(1) > 1) T2 " + andBuffer + ") A " +
                    " LEFT JOIN REPORT_WORLD_DATA_MODULAR B ON A." + config.getPrimaryKeyColumn() + " = B.DATA_ID " +
                    " AND B.DATA_CODE = '" + config.getCode() + "' " +
                    " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER C ON A." + config.getPrimaryKeyColumn() + " = C.DATA_ID " +
                    " AND C.DATA_CODE = '" + config.getCode() + "' " +
                    " LEFT JOIN REPORT_WORLD_DATA_REPORT D ON A." + config.getPrimaryKeyColumn() + " = D.DATA_ID " +
                    " AND D.DATA_CODE = '" + config.getCode() + "' " +
                    " GROUP BY B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID," + bufferOnlyColumn +
                    " HAVING COUNT(1) > 1";
            TableCheck tableCheck = new TableCheck();
            tableCheck.setId(DateUtil.generateUUID());
            tableCheck.setDatasql(sql);
            tableCheck.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
            tableCheck.setAddTime(LocalDateTime.now());
            tableCheck.setFileName("voluntarilyAdd");
            tableCheck.setErrorType(2); //警告
            tableCheck.setErrorMsg(config.getDataBaseName() + "【" + config.getCode().replace("RW_DB_", "") + "】中的数据对应所属模块，所属客户，所属报表信息相同，请检查！");
            tableCheck.setSequence(100);
            tableCheck.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
            tableCheck.setTableId(tableId);
            tableCheck.setVersion(version);
            tableCheckService.save(tableCheck);
        } catch (Exception ex) {
            throw new BusinessException("检查表数据信息出错：" + ex.getMessage());
        } finally {

        }
    }


    /**
     * 校验所有数据,生成文文件
     *
     * @param
     * @throws Exception
     */
    public String queryCheckDataBaseInfoAll() throws Exception {
        Connection connection = null;
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        String tableName = "";
        String sql = "";
        StringBuffer msgInfo = new StringBuffer();
        try {
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            List<DatabaseConfig> list = databaseConfigService.selectListDatabaseConfig();
            if (CollectionUtils.isEmpty(list)) {
                return "";
            }
            for (DatabaseConfig config : list) {
                if (null == config || StringUtils.isBlank(config.getCode()) || StringUtils.isBlank(config.getOnlyColumn())) {
                    continue;
                }
                // 查询对应表配置的检查SQL语句
                tableName = config.getCode().replace("RW_DB_", "");
                // 查询表ID
                QueryWrapper<Table> wrapper = new QueryWrapper<>();
                wrapper.eq("NAME", tableName);
                Table table = tableService.getOne(wrapper);
                if (null == table || StringUtils.isBlank(table.getId())) {
                    continue;
                }
                QueryWrapper<TableCheck> wrapperCheck = new QueryWrapper<>();
                wrapperCheck.eq("TABLE_ID", table.getId());
                wrapperCheck.orderByAsc("SEQUENCE");
                wrapperCheck.eq("STATUS", ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                List<TableCheck> listTableCheck = tableCheckService.list(wrapperCheck);
                if (CollectionUtils.isEmpty(listTableCheck)) {
                    continue;
                }

                String[] arr = config.getOnlyColumn().split(",");
                if (null == arr || arr.length == 0) {
                    continue;
                }
                for (TableCheck check : listTableCheck) {
                    if (null == check || StringUtils.isBlank(check.getDatasql())) {
                        continue;
                    }
                    sql = check.getDatasql(); // 检查的SQL语句
                    // 查询
                    pStemtSelect = connection.prepareStatement(sql);
                    rs = pStemtSelect.executeQuery();
                    StringBuffer buffer = new StringBuffer();
                    while (rs.next()) {
                        buffer.append("\r\n");
                        for (String cloum : arr) {
                            buffer.append("【").append(cloum).append("==").append(rs.getString(cloum)).append("】");
                        }
                    }
                    rs.close();
                    if (buffer.length() > 0) {
                        msgInfo.append("警告：表" + table.getName()).append("业务主键" + buffer).append("校验不通过，原因【").append(check.getErrorMsg()).append("】").append("\r\n");
                        continue;
                    }
                }
            }
            msgInfo.append(tableCheckService.checkTableAndDataSqlInfo(connection));
        } catch (Exception ex) {
            log.error(tableName + "【" + sql + "】校验语句执行失败！" + ex.getMessage());
            throw new BusinessException(tableName + "【" + sql + "】校验语句执行失败！" + ex.getMessage());
        } finally {
            if (null != connection) {
                connection.close();
            }
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemtSelect != null) {   // 关闭声明
                pStemtSelect.close();
            }
        }
        return msgInfo.toString();
    }


}
